/**
 * Created by xc on 2016/05/12.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');
var CONFIG = require('../../config');
var async = require('async');

// Schema 结构
var ItbInvoiceModel = function(obj){
    ItbBaseModel.call(this,obj);

    // 以下为表定义数据，需要的字段就填入
    //`inv_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '发票id',
    //`inv_no` varchar(100) NOT NULL COMMENT '发票编号',
    //`state` int(3) NOT NULL DEFAULT '0' COMMENT '发票状态:10待支付、11已支付、12支付失败、20开票中、22开票失败、30配送中、40完成、50取消',
    //`type` int(3) NOT NULL DEFAULT '1' COMMENT '发票类型：	1表示公司，2表示个人',
    //`title` varchar(100) NOT NULL DEFAULT '' COMMENT '发票抬头',
    //`detail` varchar(500) NOT NULL DEFAULT '' COMMENT '发票内容',
    //`total_fee` bigint(20) NOT NULL DEFAULT '0' COMMENT '总金额',
    //`taxpayer_id` varchar(100) DEFAULT '' COMMENT '公司发票：纳税人识别号(或统一社会信用代码)',
    //`bank` varchar(100) DEFAULT '' COMMENT '公司发票：开户行',
    //`bank_account` varchar(100) DEFAULT '' COMMENT '公司发票：银行帐号',
    //`addr` varchar(200) DEFAULT '' COMMENT '公司发票：地址电话（发票附属信息，非邮寄地址）',
    //`mobile` bigint(20) DEFAULT NULL COMMENT '手机号',
    //`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
    //`express` varchar(100) DEFAULT NULL COMMENT '快递公司',
    //`tracking_no` varchar(100) DEFAULT NULL COMMENT '快递单号',
    //`desc` varchar(200) DEFAULT NULL COMMENT '备注信息',
    //`create_time` datetime NOT NULL,
    //`update_time` datetime NOT NULL,
    //`update_by` varchar(50) NOT NULL,
    //`del_flg` int(1) NOT NULL DEFAULT '0',

};
ItbInvoiceModel.prototype = new ItbBaseModel();

// 取得表名
ItbInvoiceModel.prototype.getTableName = function(){
    return 'itb_invoice';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbInvoiceModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'inv_id='+this.inv_id;
}


//------------------------------------------------------------------
/* 创建发票订单
 * 参数: payedIds 支付过的订单id的数组
 * 参数: fee	 金额，对象，包括以下属性：
 *              mail_fee：发票邮费
 *              order_fee：订单丹总金额
 *              total_fee：总金额
 * 参数：content 发票内容：例如：印刷费
 * 参数: invoiceType	1表示公司，2表示个人
 * 参数: addressId 该发票订单对应的地址 id
 * 参数： callback：回调，参数如下：
 *          error:错误消息
 *          orderId:订单id，失败返回null
 * 注意：使用事务处理改更新，错误后回滚
 */
ItbInvoiceModel.prototype.createInvoice = function(order_no, userId, payedIds, fee, content, invoiceType, addressId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 创建发票订单
    var newOrderId = null;
    function createInvoiceInConnection(connection, userId, fee, callback1){
        var sql =
            " insert into itb_invoice(inv_no, type, state, content, total_fee,order_fee,mail_fee,user_id,addr_id,update_by,create_time,update_time)  "+
            " values('"+order_no+"',"+invoiceType+","+10+",'"+content+"',"+fee.total_fee+","+fee.order_fee+","+fee.mail_fee+","+userId+","+addressId+","+userId+",sysdate()"+",sysdate()"+") ";
        console.log("createInvoice" + sql);

        connection.query(sql,function(error,result){
            if(error){
                console.log(sql);
                return callback1(error, null);
            }
            if(!result){
                return callback1(error, null);
            }
            newOrderId = result.insertId;
            return callback1(error, newOrderId);
        });
    }
    // 更新订单表的发票id字段
    function updatePayedOrderInvoice(connection, payedIds, newOrderId, callback2){
        var asyncFuncsOfUpdate = [];
        for (var i in payedIds) {
            asyncFuncsOfUpdate.push((function (index, orderId) {
                return function (asyncCallback) {
                    var sql =
                        " update itb_trade_order  "+
                        " set   inv_id = "+newOrderId+
                        " where order_id="+orderId;

                    connection.query(sql,function(error,info){
                        return asyncCallback(error,info);
                    });
                }

            })(i, payedIds[i])); // 利用闭包解决动态参数传递问题
        }
        async.parallel(asyncFuncsOfUpdate, function (error, results) {
            return callback2(error, null);
        });
    }

    // 开启事务处理
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbInvoiceModel.createInvoice Err:'+err);
        }

        var trans = connection.beginTransaction(function(error){
            if (error) {
                // And done with the connection.
                connection.release();
                return callback(error,null)
            }else{
                var asynFuncs = [
                    // 创建发票订单
                    function(callback1){
                        return createInvoiceInConnection(connection, userId, fee,callback1);
                    },
                    // 更新订单表的发票id字段
                    function(callback2){
                        return updatePayedOrderInvoice(connection, payedIds,newOrderId, callback2);
                    }
                ];
                async.series(asynFuncs,function(error,results){
                    if (error) {
                        connection.rollback();

                        // And done with the connection.
                        connection.release();
                        return callback(error,null)

                    } else {
                        // 没有错误 提交事务
                        connection.commit(function(error, info) {
                            // And done with the connection.
                            connection.release();

                            if(error){
                                return callback(error, null)
                            }else{
                                return callback(null,newOrderId)
                            }
                        });
                    }
                });
            }
        });


    });
}

//------------------------------------------------------------------
/* 分页获取发票订单列表
 * 参数: userId
 * 参数: pageNum
 * 参数: pageSize
 * 参数: callback：回调
 */
ItbInvoiceModel.prototype.getInvoiceListByUserId=function(userId,pageNum,pageSize,callback){
    var THIS = this;
    var tableName = this.getTableName();

    var skipCnt = Number(pageNum||'0') * Number(pageSize||'0');

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT t.* '+
            ' FROM itb_invoice t '+
            ' WHERE t.del_flg=0 AND t.user_id='+userId+
            ' ORDER BY t.update_time DESC ';

        if(typeof skipCnt!='undefined' && skipCnt != null
            && typeof pageSize!='undefined' && pageSize != null && pageSize>0){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });
    });
}


//------------------------------------------------------------------
/* 获取地址详情
 * 参数: callback:回调，参数如下：
 *          error:
 *          address: ItbAddressModel
 */
ItbInvoiceModel.prototype.findAddressFromDB = function( callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbInvoiceModel.findAddressFromDB Err:'+err);
            return callback(err, null);
        }
        // Use the connection
        var sql = " select a.*  "+
            " from itb_invoice t, "+
            " 	itb_address a "+
            " where t.inv_id= "+ THIS.inv_id+
            " and t.del_flg=0 "+
            " and t.addr_id=a.addr_id "+
            " and a.del_flg=0 ";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error,null);
            }
            if(!result || result.length==0){
                return callback(null,null);
            }

            return callback(null,result[0]);
        });
    });
}


//------------------------------------------------------------------
/* 获取可关闭的订单
 * 参数: callback:回调
 */
ItbInvoiceModel.prototype.getAbleCloseInv = function(skipCnt, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbInvoiceModel.getAbleCloseInv Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_AUTO_CLOSE_HOUR_LIMIT || 24) * 60;
        var sql = " select * from ( "+
            " select t.* "+
            " from itb_invoice t "+
            " where t.del_flg=0 "+
            " and t.state IN (10, 12) "+
            " and t.create_time < date_sub(now(), INTERVAL "+MIN_LIMIT+" MINUTE) "+
            " order by t.create_time, t.inv_id ) tt ";

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(result && result.length>0) {
                return callback(null, result);
            } else {
                return callback(error, null);
            }
        });
    });
};
ItbInvoiceModel.prototype.getAbleCloseInvCnt = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbInvoiceModel.getAbleCloseInvCnt Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_AUTO_CLOSE_HOUR_LIMIT || 24) * 60;
        var sql =
            " select COUNT(*) AS cnt"+
            " from itb_invoice t "+
            " where t.del_flg=0 "+
            " and t.state IN (10, 12) "+
            " and t.create_time < date_sub(now(), INTERVAL "+MIN_LIMIT+" MINUTE) ";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0) {
                return callback(null, result[0].cnt||0);
            } else {
                return callback(error, 0);
            }
        });
    });
};
//------------------------------------------------------------------
/** 关闭超时订单
 */
ItbInvoiceModel.prototype.cancelOvertimeInv = function(invId,userId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbInvoiceModel.cancelOvertimeInv Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_AUTO_CLOSE_HOUR_LIMIT || 24) * 60;
        var sql =
            " update itb_invoice t "+
            " set t.state=50, t.update_time=sysdate(), t.update_by= "+userId+
            " where t.del_flg=0 "+
            " and t.state IN (10, 12) "+
            " and t.inv_id="+invId+
            " and t.create_time < date_sub(now(), INTERVAL "+MIN_LIMIT+" MINUTE) ";

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, 0);
            }
            if(result){
                return callback(null, result.affectedRows||0);
            }
            return callback(null, 0);
        });
    });
};

//------------------------------------------------------------------
/* 获取可确认的订单
 * 参数: callback:回调
 */
ItbInvoiceModel.prototype.getAbleDoneInv = function(skipCnt, pageSize, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbInvoiceModel.getAbleDoneInv Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_DONE_CLOSE_DAY_LIMIT || 7) * 1440;
        var sql = ' select * from ( '+
            ' SELECT d.* '+
            ' FROM itb_invoice d, '+
            ' (SELECT h.trade_obj_id AS inv_id, MAX(h.create_time) AS his_create_time '+
            '    FROM itb_trade_state_his h '+
            '   WHERE h.del_flg=0 AND h.type=20 and h.state=30 '+// 配送中
            '     AND h.trade_obj_id IN (SELECT t.inv_id FROM itb_invoice t WHERE t.del_flg=0 AND t.state=30) '+
            '   GROUP BY h.trade_obj_id) a '+
            ' WHERE d.inv_id=a.inv_id AND d.del_flg=0 AND d.state=30 '+
            ' AND a.his_create_time < date_sub(now(), INTERVAL '+MIN_LIMIT+' MINUTE) '+
            ' order by a.his_create_time, d.inv_id ) tt ';

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(result && result.length>0) {
                return callback(null, result);
            } else {
                return callback(error, null);
            }
        });
    });
};
ItbInvoiceModel.prototype.getAbleDoneInvCnt = function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbInvoiceModel.getAbleDoneInvCnt Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_DONE_CLOSE_DAY_LIMIT || 7) * 1440;
        var sql =
            ' select COUNT(d.inv_id) AS cnt '+
            ' FROM itb_invoice d, '+
            ' (SELECT h.trade_obj_id AS inv_id, MAX(h.create_time) AS his_create_time '+
            '    FROM itb_trade_state_his h '+
            '   WHERE h.del_flg=0 AND h.type=20 and h.state=30 '+// 配送中
            '     AND h.trade_obj_id IN (SELECT t.inv_id FROM itb_invoice t WHERE t.del_flg=0 AND t.state=30) '+
            '   GROUP BY h.trade_obj_id) a '+
            ' WHERE d.inv_id=a.inv_id AND d.del_flg=0 AND d.state=30 '+
            ' AND a.his_create_time < date_sub(now(), INTERVAL '+MIN_LIMIT+' MINUTE) ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            if(result && result.length>0) {
                return callback(null, result[0].cnt||0);
            } else {
                return callback(error, 0);
            }
        });
    });
};
//------------------------------------------------------------------
/* 确认超时订单
 */
ItbInvoiceModel.prototype.confirmOvertimeInv = function(invId, userId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbInvoiceModel.confirmOvertimeInv Err:'+err);
            return callback(err,null);
        }
        // Use the connection
        var MIN_LIMIT = Number(CONFIG.TRADE_ORDER_DONE_CLOSE_DAY_LIMIT || 7) * 1440;
        var sql =
            ' UPDATE itb_invoice d, '+
            ' (SELECT h.trade_obj_id AS inv_id, MAX(h.create_time) AS his_create_time '+
            '    FROM itb_trade_state_his h '+
            '   WHERE h.del_flg=0 AND h.type=20 and h.state=30 '+// 配送中
            '     AND h.trade_obj_id= '+invId+
            '   GROUP BY h.trade_obj_id) a '+
            ' SET d.state=40, d.update_time=sysdate(), d.update_by= '+userId+
            ' WHERE d.inv_id=a.inv_id AND d.del_flg=0 AND d.state=30 '+
            ' AND a.his_create_time < date_sub(now(), INTERVAL '+MIN_LIMIT+' MINUTE) ';

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, 0);
            }
            if(result){
                return callback(null, result.affectedRows||0);
            }
            return callback(null, 0);
        });
    });
};


// 导出
module.exports = ItbInvoiceModel;